package com.zsl.dao.impl;

import com.zsl.dao.UsersDao;
import com.zsl.model.Users;
import com.zsl.util.C3P0Util;
import com.zsl.util.DbHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl implements UsersDao {

    @Override
    public List<Users> getAllUser() throws SQLException {
        StringBuilder sql = null;
        List<Users> users = new ArrayList<>();
        sql = new StringBuilder("select * from users");
        ResultSet rs = DbHelper.executeQuery(sql.toString(),null);
        while (rs.next()){
            Users users1 = new Users();
            users1.setId(rs.getString("id"));
            users1.setUsername(rs.getString("username"));
            users1.setPassword(rs.getString("password"));
            users1.setCellphone(rs.getString("cellphone"));
            users1.setMobilephone(rs.getString("mobilephone"));
            users1.setAddress(rs.getString("address"));
            users1.setEmail(rs.getString("email"));
            users.add(users1);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return users;
    }

    @Override
    public Users getOneUserById(String id) throws SQLException {
        String sql = "select * from users where id =?";
        ResultSet rs = DbHelper.executeQuery(sql,new Object[]{id});
        Users users = new Users();
        while(rs.next()){
            users.setId(rs.getString("id"));
            users.setUsername(rs.getString("username"));
            users.setPassword(rs.getString("password"));
            users.setCellphone(rs.getString("cellphone"));
            users.setMobilephone(rs.getString("mobilephone"));
            users.setAddress(rs.getString("address"));
            users.setEmail(rs.getString("email"));
        }
        return users;
    }

    @Override
    public boolean isRealUser(String uname, String pwd) throws SQLException {
        String flag = null;
        String sql = "select count(*) from users where username= ? and password=?";
        //String[] params = {uname, pwd};
        ResultSet rs = DbHelper.executeQuery(sql, new Object[]{uname,pwd});
        if (rs.next()){
            flag = rs.getString(1);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        if(flag.equals("1")){
            return true;
        }else
            return false;
    }

    @Override
    public Users getUserByName(String uname) throws SQLException {
        String sql = "select * from users where username =?";
        ResultSet rs = DbHelper.executeQuery(sql,new Object[]{uname});
        Users users = new Users();
        while(rs.next()){
            users.setId(rs.getString("id"));
            users.setUsername(rs.getString("username"));
            users.setPassword(rs.getString("password"));
            users.setCellphone(rs.getString("cellphone"));
            users.setMobilephone(rs.getString("mobilephone"));
            users.setAddress(rs.getString("address"));
            users.setEmail(rs.getString("email"));
        }
        return users;
    }

    @Override
    public Integer insertUser(Users users) {
        String sql="insert into users(id,username,password,cellphone,mobilephone,address,email,is_deleted)values(?,?,?,?,?,?,?,'0')";
        return DbHelper.executeUpdate(sql,new Object[]{
                users.getId(),
                users.getUsername(),
                users.getPassword(),
                users.getCellphone(),
                users.getMobilephone(),
                users.getAddress(),
                users.getEmail()
        });
    }

    @Override
    public Integer deleteUserById(String user_id) {
        String sql = "delete from users where id = ?";
        return DbHelper.executeDelete(sql,new Object[]{user_id});
    }

}
